package cn.edu.zjgsu.ito.service;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;

public class AdminUploadPreScoreService { 
	private POIFSFileSystem fs;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;
    private HSSFRow row;
	/**
	 * 上传学生历史成绩
	 * 杨昆鹏
	 * */
	
	//验证数据合法
	public List<String> verifyData(InputStream is){
		try {
            fs = new POIFSFileSystem(is);
            wb = new HSSFWorkbook(fs);
        
	        sheet = wb.getSheetAt(0);
	        row = sheet.getRow(0);
	        List<String> uploadInf = new ArrayList<String>();//一条记录状态
//	        String errorSco= "";//数据库中没有该门课程
	        int succSco= 0;
	        int repeat = 0;//数据重复
	        int illegal = 0;//数据非法
	        
	        //非法数据提醒
//	        String errorrow = "";//行学号不合格
//	        String errorscore = "";//行成绩格式不合格
//	        String errorstatus = "";//行考试类型格式不合格
//	        String errorrep = "";//行数据重复
	        
	        int data = 0;
	        
	        int colNum = 0;
	        int rowNum = 0;
	        String reuuid = "";
	        
	        String term = (sheet.getRow(0).getCell(1)).toString();
	      //验证课程是否是数据库中含有的
	        String course = (sheet.getRow(0).getCell(4)).toString();
	        String courseNo = "";//课程号
        	List<Record> coursedb = Db.find("select * from course");//课程名
        	List<Record> stuPreInf = Db.find("select * from student_grade");//历史表中学生信息
        	List<Record> stuInf = Db.find("select * from student_info");//历史表中学生信息
        	for(int a = 0;a < coursedb.size();a++){
        		if(course.equals(coursedb.get(a).getStr("courseName"))){
        			System.out.println("数据库中有该课程");
        			
        			Record courseno = Db.findFirst("select courseNo from course where courseName=?",course);
        			courseNo = courseno.getStr("courseNo");
        			System.out.println(courseNo);
//        			reuuid = uuids.toString();
        			reuuid = term + courseNo;
        			colNum = row.getPhysicalNumberOfCells();//总列数
        	        rowNum = sheet.getPhysicalNumberOfRows();//总行数
        	        if(Db.findFirst("select * from test where testNo=?",reuuid) == null){
        	        	Db.save("test",new Record().set("termValue",term).set("courseNo",courseNo).set("testType","3").set("uuid",reuuid).set("testNo",reuuid));
        	        }
        	        succSco += 1;
        	        break;
        		}
        	}
        	

        	//数据库中加上相对的课程
        	
        	Record recourse = Db.findFirst("select * from teacher_timetable where uuid=?",reuuid);
        	
        	
        	if(recourse == null){
        		Record teaTimetable = new Record().set("termValue",term).set("courseName",course)
            			.set("courseNo",courseNo).set("uuid",reuuid);
        		Db.save("teacher_timetable",teaTimetable);
        	}
        	
        	if(succSco == 1){
        		
        	}else{
        		List<String> errorSco = new ArrayList<String>();
        		return errorSco;
        	}
        	
//        	List<Record> courseNo = Db.find("select * from major_timetable where majorName=?",course);//课程名对应的课程号
//			String courseno = courseNo.get(0).getStr("courseNo");
        	

//循环遍历
        	for (int j = 2; j < rowNum; j++) {
        		int isStu = 0;
        		int temp = 0;
	        	HSSFRow rowj = sheet.getRow(j);
	        	HSSFCell cell = null  ;
	        	for(int i = 1; i < colNum; i++){
	        		cell = rowj.getCell(i);//假如row.getCell(0)中的数值为12345678910123  
	                cell.setCellType(HSSFCell.CELL_TYPE_STRING);  
//	                System.out.println("sssssssssssss");
	        	}
	        	String sno = (sheet.getRow(j).getCell(1)).toString();
	        	String newpassportno =  (sheet.getRow(j).getCell(2)).toString();
	        	Record newpass = Db.findFirst("select * from newpassportno where newpassportNo=?",newpassportno);
	        	String passportno = "";
	        	if(newpass != null){
	        		passportno = newpass.getStr("passportNo");
	        	}
	        	String grade = (sheet.getRow(j).getCell(5)).toString();
	        	String gradeStatus = (sheet.getRow(j).getCell(6)).toString();
	        	String firstname = (sheet.getRow(j).getCell(4)).toString();
	        	String lastname = (sheet.getRow(j).getCell(3)).toString();
	        	
	        	//判断数据合法性
	        	//判断学生护照合法性
	        	for(int a = 0;a < stuInf.size();a++){
	        		if(passportno.equals(stuInf.get(a).getStr("passportNo"))){
	        			
	        			if(sno.equals(stuInf.get(a).getStr("sNo"))){
			        			isStu = 1;
			        			System.out.println("数据库中有该学生护照号");
		    	    	    }
		        		}
	        		}
	        	if(isStu == 1){
	        		//判断学号是否为十位数字
//		        	if(sno.length() == 10){
//		        		Pattern pattern = Pattern.compile("[0-9]+");  
//	    	    	    Matcher matcher = pattern.matcher((CharSequence) sno);  
//	    	    	    boolean sNoRes = matcher.matches(); 
//	    	    	    if(sNoRes){
//	    	    	    	System.out.println("学号合格");
//	    	    	    }else{
//	    	    	    	String errorrow = j+1 + "行学号不合格";
//	    	    	    	uploadInf.add(errorrow);
//	    	    	    	System.out.println(errorrow);
//	    	    	    	illegal++;
//	    	    	    	continue;
		        	//判断成绩格式
		        	if(grade.equals("优秀")||grade.equals("良好")||grade.equals("中等")||grade.equals("及格")||grade.equals("不及格")){
		        		System.out.println("成绩合格1");
		        	}else{
		        		int gradees = Integer.parseInt(grade);
		        		if(gradees >= 0 && gradees <= 100){
		        			System.out.println("成绩合格");
		        		}else{
		        			String errorscore = j+1 + "行成绩格式不合格";
		        			uploadInf.add(errorscore);
		        			System.out.println(errorscore);
		        			illegal++;
			        		continue;
		        		}
		        	}

		        	//判断考试成绩类型
		        	if(gradeStatus.equals("1") || gradeStatus.equals("2")){
		        		System.out.println("考试类型合格");
		        	}else{
		        		String errorstatus = j+1 + "行考试类型格式不合格";
		        		uploadInf.add(errorstatus);
		        		System.out.println(errorstatus);
		        		illegal++;
		        		continue;
		        	}
		        	
		        	//判断数据重复
		        	for(int b = 0;b < stuPreInf.size();b++){
		        		if(term.equals(stuPreInf.get(b).getStr("termValue")) && passportno.equals(stuPreInf.get(b).getStr("passportNo"))
	  	        			 && course.equals(stuPreInf.get(b).getStr("courseName")) && gradeStatus.equals(stuPreInf.get(b).getInt("gradeStatus").toString())){
		        			
		        			Db.delete("student_grade", stuPreInf.get(b));
		        			Record updatarecord = new Record().set("termValue",term).set("courseName",course).set("passportNo",passportno)
//		        					.set("lastName",lastname).set("firstName",firstname)
		        					.set("courseNo",courseNo).set("grade",grade).set("gradeStatus",gradeStatus).set("uuid",reuuid).set("testNo",reuuid);
		        			Db.save("student_grade",updatarecord );
		        			String errorrep = j+1 + "行数据重复，已更新";
		        			uploadInf.add(errorrep);
		        			System.out.println(errorrep);
		        			repeat++;
		        			temp += 1;
		        			break;
		        		}
	    	       	}
		        	
		        	
		        	//新的符合标准数据
		        	if(temp == 0){
		        		Record updatarecord = new Record().set("termValue",term).set("courseName",course).set("passportNo",passportno)
//		        				.set("lastName",lastname).set("firstName",firstname)
		        				.set("courseNo",courseNo).set("grade",grade).set("gradeStatus",gradeStatus).set("uuid",reuuid).set("testNo",reuuid);
		    			Db.save("student_grade",updatarecord );
		    			data++;
		        	}
	        		
	        	}else{
	        		illegal++;
	        		String errPassport = j+1 + "行学生个人信息不合格。" ; 
	        		uploadInf.add(errPassport);
	        		System.out.println(errPassport);
	        	}
	        	
	        	
		    }
        	String result = repeat+"重复更新"+"----"+illegal+"错误"+"----"+data+"新数据";
        	uploadInf.add(result);
        	return  uploadInf;
        } catch (IOException e) {
            e.printStackTrace();
        }finally{
        	try {
				is.close();
			} catch (IOException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
        }
		return null;
	}
	

}

